﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace telefonia
{
    public static class DataBase
    {
        public const string connectionString = @"Data Source=.\sqlexpress;Initial Catalog=TELEFONIA;Integrated Security=True";
        private static SqlConnection cnn;

        public static bool AddClient(ClientModel client)
        {
            //ado.net
            cnn = new SqlConnection(connectionString);
            string sqlSentence = @"INSERT INTO [dbo].[Cliente]
                                   ([nombres]
                                   ,[apellidos]
                                   ,[ci]
                                   ,[lugar_emision_ci]
                                   ,[telefono_contacto]
                                   ,[email]
                                   ,[persona_contacto]
                                   ,[departamento_residencia]
                                   ,[localidad]
                                   ,[direccion_domicilio]
                                   ,[direccion_laboral])
                             VALUES
                                    (@nombres
                                   ,@apellidos
                                   ,@ci
                                   ,@lugar_emision_ci
                                   ,@telefono_contacto
                                   ,@email
                                   ,@persona_contacto
                                   ,@departamento_residencia
                                   ,@localidad
                                   ,@direccion_domicilio
                                   ,@direccion_laboral)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@nombres", client.Nombres);
            cmd.Parameters.AddWithValue("@apellidos", client.Apellidos);
            cmd.Parameters.AddWithValue("@ci", client.CI);
            cmd.Parameters.AddWithValue("@lugar_emision_ci", client.LugarEmisionCI);
            cmd.Parameters.AddWithValue("@telefono_contacto", client.TelefonoContacto);
            cmd.Parameters.AddWithValue("@email", client.Email);
            cmd.Parameters.AddWithValue("@persona_contacto", client.PersonaContacto);
            cmd.Parameters.AddWithValue("@departamento_residencia", client.DepartamentoResidencia);
            cmd.Parameters.AddWithValue("@localidad", client.Email);
            cmd.Parameters.AddWithValue("@direccion_domicilio", client.DireccionDomicilio);
            cmd.Parameters.AddWithValue("@direccion_laboral", client.DireccionLaboral);

            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("The record wasn't registered");
                }
                else { return true; }

            }
            catch (Exception e)
            {
                throw new ArgumentException(e.Message.ToString());

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

        public static bool AddPromocionFidelidad(PromocionFidelidadModel fide)
        {
            cnn = new SqlConnection(connectionString);
            string sqlSentence = @"INSERT INTO [dbo].[PromocionFidelidad]
           ([numero]
           ,[tipo]
           ,[fecha_inicio]
           ,[fecha_fin])
     VALUES
           (@numero,@tipo,@fecha_inicio,@fecha_fin)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@numero", fide.Numero);
            cmd.Parameters.AddWithValue("@tipo", fide.Tipo);
            cmd.Parameters.AddWithValue("@fecha_inicio", fide.FechaInicio);
            cmd.Parameters.AddWithValue("@fecha_fin", fide.FechaFin);

            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("The record wasn't registered");
                }
                else { return true; }

            }
            catch (Exception e)
            {
                throw new ArgumentException(e.Message.ToString());

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

       

        public static DataTable GetReports(string query)
        {          
            DataTable dt = new DataTable();
            cnn = new SqlConnection(connectionString);
           
            try
            {
                SqlCommand cmd = new SqlCommand(query, cnn);
                cnn.Open();
                // create data adapter
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                // this will query your database and return the result to your datatable
                da.Fill(dt);
                cnn.Close();
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return dt;
        }

        public static bool AddReclamo(ReclamoModel reclamo)
        {
            cnn = new SqlConnection(connectionString);
            string sqlSentence = @"INSERT INTO [dbo].[Reclamo]
                                   ([cod_reclamo]
                                   ,[celular]
                                   ,[tipo]
                                   ,[fecha]
                                   ,[motivo])
                             VALUES
                                   (@cod_reclamo
                                   ,@celular
                                   ,@tipo
                                   ,@fecha
                                   ,@motivo)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@cod_reclamo", reclamo.Codigo);
            cmd.Parameters.AddWithValue("@celular", reclamo.Celular);
            cmd.Parameters.AddWithValue("@tipo", reclamo.Tipo);
            cmd.Parameters.AddWithValue("@fecha", reclamo.Fecha);
            cmd.Parameters.AddWithValue("@motivo", reclamo.Motivo);
            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("No hemos podido registrar");
                }
                else
                {
                    return true;
                }

            }
            catch (Exception e)
            {
                throw new ArgumentException(e.Message.ToString());

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public static bool AddPromocionLlamadas(PromocionLlamada llam) {
            cnn = new SqlConnection(connectionString);
            string sqlSentence = @"INSERT INTO [dbo].[PromocionLlamada]
                                   ([numero]
                                   ,[duracion_llamada]
                                   ,[fecha_llamada]
                                   ,[destino]
                                   ,[tipo_llamada]
                                   ,[linea])
                             VALUES
                                   (@numero
                                   ,@duracion_llamada
                                   ,@fecha_llamada
                                   ,@destino
                                   ,@tipo_llamada
                                   ,@linea)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@numero", llam.Numero);
            cmd.Parameters.AddWithValue("@duracion_llamada", llam.Duracion);
            cmd.Parameters.AddWithValue("@fecha_llamada", llam.Fecha);
            cmd.Parameters.AddWithValue("@destino", llam.Destino);
            cmd.Parameters.AddWithValue("@tipo_llamada", llam.Tipo);
            cmd.Parameters.AddWithValue("@linea", llam.Linea);
            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("No hemos podido registrar");
                }
                else
                {
                    return true;
                }

            }
            catch (Exception e)
            {
                throw new ArgumentException(e.Message.ToString());

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
        public static DataTable GetReclamos()
        {
            DataTable dt = new DataTable();
            cnn = new SqlConnection(connectionString);
            string query = "SELECT * FROM [TELEFONIA].[dbo].[Reclamo]";
            try
            {
                SqlCommand cmd = new SqlCommand(query, cnn);
                cnn.Open();
                // create data adapter
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                // this will query your database and return the result to your datatable
                da.Fill(dt);
                cnn.Close();
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return dt;
        }

        public static bool AddCelular(CelularModel cel)
        {
            cnn = new SqlConnection(connectionString);
            string sqlSentence = @"INSERT INTO[dbo].[Celular]
                                           ([numero]
                                           ,[ci]
                                           ,[empresa]
                                           ,[linea]
                                           ,[modelo])
                                     VALUES
                                           (@numero
                                           ,@ci
                                           ,@empresa
                                           ,@linea
                                           ,@modelo)";
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sqlSentence;
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.AddWithValue("@numero", cel.Numero);
            cmd.Parameters.AddWithValue("@ci", cel.Cliente);
            cmd.Parameters.AddWithValue("@empresa", cel.Empresa);
            cmd.Parameters.AddWithValue("@linea", cel.Linea);
            cmd.Parameters.AddWithValue("@modelo", cel.Modelo);
            try
            {
                cnn.Open();
                int value = cmd.ExecuteNonQuery();
                if (value < 1)
                {
                    throw new ArgumentException("The record wasn't registered");
                }
                else { return true; }

            }
            catch (Exception e)
            {
                throw new ArgumentException(e.Message.ToString());

            }
            finally
            {
                if (cnn.State == System.Data.ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }

        public static DataTable GetClients()
        {
            DataTable dt = new DataTable();
            cnn = new SqlConnection(connectionString);
            string query = "SELECT * FROM [TELEFONIA].[dbo].[Cliente]";
            try
            {
                SqlCommand cmd = new SqlCommand(query, cnn);
                cnn.Open();
                // create data adapter
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                // this will query your database and return the result to your datatable
                da.Fill(dt);
                cnn.Close();
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return dt;

        }
        public static DataTable GetCelulares()
        {
            DataTable dt = new DataTable();
            cnn = new SqlConnection(connectionString);
            string query = "SELECT * FROM [TELEFONIA].[dbo].[Celular]";
            try
            {
                SqlCommand cmd = new SqlCommand(query, cnn);
                cnn.Open();
                // create data adapter
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                // this will query your database and return the result to your datatable
                da.Fill(dt);
                cnn.Close();
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return dt;

        }
    }
}
